package Dao.QueryCourseDao;

import Entity.Course;
import Entity.CoursePlus;
import Entity.StudentCourse;
import Entity.Teacher;
import Util.SQLParse;
import Util.SQLUtil;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/*
专门负责教师 学生 管理员查询课程等信息的 SQLDao
 */
public class QueryCourseSQLDao {

    private SQLUtil sqlUtil = new SQLUtil();

    /*
    管理员查询审核未教师发布的课程 通过状态  适用于管理员
    需要参数 状态
    返回一个课程链表
     */
    public List<Course> queryCourseBystatus(int statu) {
        Object object[] = new Object[]{statu};
        String sql = "select *from courseinfo where status = ?";

        ResultSet rs = sqlUtil.executeQuery(sql, object);

        return SQLParse.parseToCourse(rs);

    }


    /*
    管理员查询课程信息
    不需要参数
    返回一个课程链表
     */


    public List<Course> queryAllCourse() {
        String sql = "select *from courseinfo";

        ResultSet rs = sqlUtil.executeQuery(sql, null);

        return SQLParse.parseToCourse(rs);

    }

    /*
    学生查询未选过的课程 并且已经通过审核
    需要参数 学生id
    返回一个课程链表
     */
    public List<Course> queryOnlineCourseByStudent(String studentId){
        Object object[] = new Object[]{studentId};
        String sql =  "SELECT *from courseinfo where courseId not in(SELECT courseId from selectcourseinfo where studentId = ?) and status = 1";
        ResultSet rs = sqlUtil.executeQuery(sql, object);
        return SQLParse.parseToCourse(rs);
    }


    /*
    查询学生已经选课程
    需要参数 学生Id
    返回一个课程链表
     */
    public List<CoursePlus> querySelectedCourseByStudent(String studentId){
        Object object[] = new Object[]{studentId};
        String sql =  "SELECT c.*,s.score,t.teacherName from courseinfo c INNER JOIN selectcourseinfo s INNER JOIN teacherinfo t on c.courseId = s.courseId and c.teacherId = t.teacherId where s.studentId= ?";
        ResultSet rs = sqlUtil.executeQuery(sql, object);
        return SQLParse.parseToCoursePlus(rs);
    }


    /*
    返回教师发布的课程
    不需要参数  教师Id
    返回一个课程链表
     */
    public List<Course> queryReleaseCourseByTeacher(String teacherId){
        Object object[] = new Object[]{teacherId};
        String sql = "SELECT *from courseinfo where teacherId = ?";
        ResultSet rs = sqlUtil.executeQuery(sql,object);
        return SQLParse.parseToCourse(rs);
    }



    /*
    返回教师查询学生的课程以及成绩 姓名
    需要参数 教师Id
    返回一个课程与学生部分组合而成的聊表
     */
    public List<StudentCourse> queryStudentCourseAndScore(String teacherId){
        Object object[] = new Object[]{teacherId};
        String sql = "select c.*,s.studentId, s.score,stu.studentName from selectcourseinfo s INNER JOIN courseinfo c INNER JOIN studentinfo stu  on c.courseId =s.courseId and stu.studentId = s.studentId WHERE s.teacherId = ?";
        ResultSet rs = sqlUtil.executeQuery(sql,object);
        return  SQLParse.parseToStudentCourse(rs);
    }



}
